package com.vcg.community.common.utils;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;

import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.locale.converters.DateLocaleConverter;
import org.apache.commons.beanutils.locale.converters.StringLocaleConverter;

public class ExcelUtil {
	
	
	public static List<List<List<Object>>> readExcelToList(InputStream excelFile) throws BiffException, IOException{
		
		//将Excel读取成map
		Map<String, List<List<Object>>> readExcelToMap = readExcelToMap(excelFile);
		
		//获取工作簿
		Set<String> sheets = readExcelToMap.keySet();
		
		//用于存放每个工作簿数据
		List<List<List<Object>>> lists=new ArrayList<List<List<Object>>>();
		
		//遍历工作簿数据,并添加到list中
		for (String sheet : sheets) {
			List<List<Object>> list = readExcelToMap.get(sheet);
			lists.add(list);
		}
		return lists;
	}
	
	/**
	 * Map<String,List<List<Object>>> key代表sheet工作簿,List代表工作簿所有行,嵌套List代表每行数据
	 * @param excelFile 
	 * @return
	 * @throws BiffException
	 * @throws IOException
	 */
	public static Map<String, List<List<Object>>> readExcelToMap(InputStream excelFile) throws BiffException, IOException{
		
		Workbook work = Workbook.getWorkbook(excelFile);
		
		//获取所有工作博
		Sheet[] sheets = work.getSheets();
		
		//新建Map用于存放读取的数据
		Map<String, List<List<Object>>> map=new LinkedHashMap<String, List<List<Object>>>();
		
		for (Sheet sheet : sheets) {
			//存放工作博数据
			List<List<Object>> colunmLists=new ArrayList<List<Object>>(); 
			
			//获取每行有多少列
			int rows = sheet.getRows();
			
			//遍历每行数据
			for (int i = 0; i < rows; i++) {
				
				//获取工作簿有多少行
				int columns = sheet.getColumns();
				
				//用于存放一行数据
				List<Object> colunmList=new ArrayList<Object>();
				
				//
				int emptyColunm=0;
				for (int j = 0; j < columns; j++) {
					//获取单元格数据
					String contents = sheet.getCell(j, i).getContents();
					colunmList.add(contents);
					//过滤空单元格
					if(contents==null||contents.equals("")){
						emptyColunm+=1;
					}
				}
				
				//过滤空行
				if(emptyColunm==columns){
					continue;
				}
				
				//添加一行数据到工作簿List中
				colunmLists.add(colunmList);
			}
			
			//将每个工作薄数据put进去
			map.put(sheet.getName(), colunmLists);
		}
		return map;
	}
	
	
	/**
	 * 如果存在map,请用linkedHashMap来保证字段的顺序
	 * @param work
	 * @param dataList List<List<Object>> 一个List<Object>代表一条数据.
	 * @param viewMap 设置表头,单元格宽度 请用linkedHashMap来保证字段的顺序
	 * @param sheetName 工作薄名称
	 */
	public static void writeExcelByLists(WritableWorkbook work,List<List<Object>> dataList,Map<String, Integer> viewMap,String sheetName) throws WriteException, IOException{
		
		//获取所有已经存在的sheet
		WritableSheet[] sheets = work.getSheets();

		//创建sheet
		WritableSheet sheet = work.createSheet(sheetName, sheets.length);
		
		//设置样式
		WritableCellFormat format = getColunmFormat();
		
		//设置宽,表头信息
		setWidthLeng(sheet, viewMap, format);
		
		//遍历每行的内容
		for (int row = 0; row < dataList.size(); row++) {
			//每行数据
			List<Object> colunms = dataList.get(row);
			//每行数据
			for (int line = 0; line < colunms.size(); line++) {
				Object value = colunms.get(line);
				getObjectTypeAndWrite(sheet,format,value, line, row+1);
			}
		}
	}
	
	/**
	 * 如果存在map,请用linkedHashMap来保证字段的顺序
	 * @param work
	 * @param dataList List<Map<K, V>> 一个Map代表一条数据,请用linkedHashMap保证数据的顺序
	 * @param viewMap 设置表头,单元格宽度 请用linkedHashMap来保证字段的顺序
	 * @param sheetName 工作薄名称
	 */
	public static <K,V> void writeExcelByListMaps(WritableWorkbook work,List<Map<K, V>> dataList,Map<String, Integer> viewMap,String sheetName) throws WriteException, IOException{
		
		//获取所有已经存在的sheet
		WritableSheet[] sheets = work.getSheets();

		//创建sheet
		WritableSheet sheet = work.createSheet(sheetName, sheets.length);
		
		//设置样式
		WritableCellFormat format = getColunmFormat();
		
		//设置宽,表头信息
		setWidthLeng(sheet, viewMap, format);
		
		//遍历每行的内容
		for (int row = 0; row < dataList.size(); row++) {
			//行内容
			Map<K, V> lineDate = dataList.get(row);
			//行号
			ArrayList<Object> colunms = new ArrayList<Object>(lineDate.keySet());
			//每行数据
			for (int line = 0; line < colunms.size(); line++) {
				Object value = lineDate.get(colunms.get(line));
				getObjectTypeAndWrite(sheet,format,value, line, row+1);
			}
		}
	}
	
	/**
	 * 如果存在map,请用linkedHashMap来保证字段的顺序
	 * @param work 
	 * @param dataList 数据List<Object> 一个object代表一条数据.
	 * @param viewMap 设置表头,单元格宽度 请用linkedHashMap来保证字段的顺序
	 * @param filterList 过滤需要的字段
	 * @param sheetName 工作簿名称
	 */
	public static <T> void writeExcelByObjects(WritableWorkbook work,List<T> dataList,Map<String, Integer> viewMap,String sheetName) throws RowsExceededException, WriteException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, IOException{

		List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
		
		for (Object object : dataList) {
			Map<String, Object> beanToMap = beanToMap(object);
			list.add(beanToMap);
		}
		
		writeExcelByListMaps(work, list, viewMap, sheetName);
		
	}
	
	/**
	 * 如果存在map,请用linkedHashMap来保证字段的顺序
	 * @param work 
	 * @param dataList 数据List<Object> 一个object代表一条数据.
	 * @param viewMap 设置表头,单元格宽度 请用linkedHashMap来保证字段的顺序
	 * @param filterList 过滤需要的字段
	 * @param sheetName 工作簿名称
	 */
	public static void writeExcelByObjectsAndFilterColunm(WritableWorkbook work,List<Object> dataList,Map<String, Integer> viewMap,List<String> filterList,String sheetName) throws RowsExceededException, WriteException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, IOException{
		
		List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
		
		for (Object object : dataList) {
			Map<String, Object> beanToMap = beanToMap(object);
			Map<String, Object> filterMap=new LinkedHashMap<String, Object>();
			for (Object filter : filterList) {
				Object value = beanToMap.get(filter.toString());
				filterMap.put(filter.toString(), value);
			}
			list.add(filterMap);
		}
		
		writeExcelByListMaps(work, list, viewMap, sheetName);
		
	}
	
	/**
	 * 如果存在map,请用linkedHashMap来保证字段的顺序
	 * @param work 
	 * @param dataList 数据List<Object> 一个object代表一条数据.
	 * @param viewMap 设置表头,单元格宽度 请用linkedHashMap来保证字段的顺序
	 * @param maps 用于追加object对象字段,List<Map<String,Object>> String代表字段名,OBject代表字段值
	 * @param sheetName	工作簿名
	 */
	public static void writeExcelByObjectsAndMaps(WritableWorkbook work,List<Object> dataList,Map<String, Integer> viewMap,List<Map<String, Object>> maps,String sheetName) throws RowsExceededException, WriteException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, IOException{
		
		List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();

		for (int i = 0; i < dataList.size(); i++) {
			Object object = dataList.get(i);
			Map<String, Object> beanToMap = beanToMap(object);
			for (int j = 0; j < maps.size(); j++) {
				Map<String, Object> map = maps.get(i);
				beanToMap.putAll(map);
			}
		}

		writeExcelByListMaps(work, list, viewMap, sheetName);
	}
	
	/**
	 * 如果存在map,请用linkedHashMap来保证字段的顺序
	 * @param work 
	 * @param dataList 数据List<Object> 一个object代表一条数据.
	 * @param viewMap 设置表头,单元格宽度 请用linkedHashMap来保证字段的顺序
	 * @param maps 用于追加object对象字段,List<Map<String,Object>> String代表字段名,OBject代表字段值
	 * @param filterList 过滤需要的字段
	 * @param sheetName	工作簿名
	 */
	public static void writeExcelByObjectsAndMapsAndFilter(WritableWorkbook work,List<Object> dataList,Map<String, Integer> viewMap,List<Map<String, Object>> maps,List<String> filterList,String sheetName) throws RowsExceededException, WriteException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, IOException{
		
		List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();

		for (int i = 0; i < dataList.size(); i++) {
			Object object = dataList.get(i);
			Map<String, Object> beanToMap = beanToMap(object);
			if(i<maps.size()) {
				Map<String, Object> map = maps.get(i);
				beanToMap.putAll(map);
			}
			Map<String, Object> filterMap=new LinkedHashMap<String, Object>();
			for (int j = 0; j < filterList.size(); j++) {
				String fieldName = filterList.get(j);
				Object value = beanToMap.get(fieldName);
				filterMap.put(fieldName, value);
			}
			
			list.add(filterMap);
		}

		writeExcelByListMaps(work, list, viewMap, sheetName);
	}
	
	
	public static Map<String, Object> beanToMap(Object object) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException{
		if(object==null||object.equals("")){
			return new HashMap<String, Object>();
		}
		
		//bean转换成map对象
		Map<String, String> map = BeanUtils.describe(object);
		
		//获取bean私有属性,便于排序
		Field[] fields = object.getClass().getDeclaredFields();
		
		//用于存放已经排序好的对象
		Map<String, Object> sortMap=new LinkedHashMap<String, Object>();
		
		//遍历排序
		for (Field field : fields) {
			String fieldName = field.getName();
			Object value = map.get(fieldName);
			sortMap.put(fieldName, value);
		}
		
		return sortMap;
	}
	
	
	/**
	 * 将Excel写入输入流中
	 * @param dataList
	 * @param viewMap
	 * @param sheetName
	 * @return
	 */
	
	public static InputStream writeToInputStreamByListsList(List<List<Object>> dataList,Map<String, Integer> viewMap,String sheetName) throws WriteException, IOException{
		
		ByteArrayOutputStream os = writeToOutStreamByListsList(dataList, viewMap, sheetName);
		ByteArrayInputStream in = new ByteArrayInputStream(os.toByteArray());
		os.close();
		return in;
	}
	
	
	/**
	 * 将Excel写入输出流中
	 * @param dataList
	 * @param viewMap
	 * @param sheetName
	 * @return
	 */
	public static ByteArrayOutputStream writeToOutStreamByListsList(List<List<Object>> dataList,Map<String, Integer> viewMap,String sheetName) throws WriteException, IOException{
		ByteArrayOutputStream os=new ByteArrayOutputStream();
		WritableWorkbook work=Workbook.createWorkbook(os);
		writeExcelByLists(work, dataList, viewMap, sheetName);
		work.write();
		work.close();
		return os;
	}
	
	/**
	 * 将Excel写入输出流中
	 * @param dataList
	 * @param viewMap
	 * @param sheetName
	 */
	public static <K,V> ByteArrayOutputStream writeToOutputStreamByMaps(List<Map<K, V>> dataList,Map<String, Integer> viewMap,String sheetName) throws WriteException, IOException{
		ByteArrayOutputStream os=new ByteArrayOutputStream();
		WritableWorkbook work=Workbook.createWorkbook(os);
		writeExcelByListMaps(work, dataList, viewMap, sheetName);
		work.write();
		work.close();
		return os;
	}
	
	/**
	 * 将Excel写入输入流中
	 */
	public static <K,V> InputStream writeToInputStreamByMaps(List<Map<K, V>> dataList,Map<String, Integer> viewMap,String sheetName) throws WriteException, IOException{
		ByteArrayOutputStream os = writeToOutputStreamByMaps(dataList, viewMap, sheetName);
		ByteArrayInputStream in = new ByteArrayInputStream(os.toByteArray());
		os.close();
		return in;
	}
	
	/**
	 * 将Excel写入数组中
	 */
	public static <K,V> byte[] writeToBytesByMaps(List<Map<K, V>> dataList,Map<String, Integer> viewMap,String sheetName) throws WriteException, IOException{
		ByteArrayOutputStream os = writeToOutputStreamByMaps(dataList, viewMap, sheetName);
		byte[] bytes = os.toByteArray();
		os.close();
		return bytes;
	}
	
	/**
	 * 将Excel写入数组中
	 */
	public static byte[] writeToBytesByLists(List<List<Object>> dataList,Map<String, Integer> viewMap,String sheetName) throws WriteException, IOException{
		ByteArrayOutputStream os = writeToOutStreamByListsList(dataList, viewMap, sheetName);
		byte[] bytes = os.toByteArray();
		os.close();
		return bytes;
	}
	
	
	
	/**
	 * 对格式有严格的要求,容易出错.第一行必须为对象的字段名
	 * @param excelFile
	 * @param obj 
	 */
	@SuppressWarnings("unchecked")
	public static <T> List<List<T>> readExcelToObjects(InputStream excelFile,T obj) throws BiffException, IOException, InstantiationException, IllegalAccessException, InvocationTargetException{
		List<List<T>> list=new ArrayList<List<T>>();
		List<List<List<Object>>> readExcelToList = readExcelToList(excelFile);
		Field[] fields = obj.getClass().getDeclaredFields();
		
		for (List<List<Object>> lists : readExcelToList) {
			List<T> objList=new ArrayList<T>();
			for (List<Object> colunms : lists) {
				Object object = obj.getClass().newInstance();
				for (int i = 0; i < colunms.size(); i++) {
					String filedName = fields[i].getName();
					BeanUtils.copyProperty(object, filedName, colunms.get(i));
				}
				objList.add((T)object);
			}
			list.add(objList);
		}
		return list;
	}
	
	public static void setWidthLeng(WritableSheet sheet,Map<String, Integer> viewMap,WritableCellFormat format) throws RowsExceededException, WriteException{
		
		if(viewMap==null){
			return;
		}
		
		ArrayList<String> colunms = new ArrayList<String>(viewMap.keySet());
		
		for (int i = 0; i < colunms.size(); i++) {
			String colunm = colunms.get(i);
			Integer width = viewMap.get(colunm);
			
			if(width==null||width.equals("")){
				width=10;
			}
			sheet.setColumnView(i, width);
			sheet.addCell(new Label(i, 0, colunm, format));
		}
	}
	
	public static SimpleDateFormat sf=new SimpleDateFormat("yyyy-MM-dd");
	
	public static void getObjectTypeAndWrite(WritableSheet sheet,WritableCellFormat format,Object value,int row,int line) throws RowsExceededException, WriteException{
		if(value==null){
			sheet.addCell(new Label(row, line, "", format));
			return;
		}
		
		if (value instanceof Float) {
			sheet.addCell(new Number(row, line, Double.parseDouble(value.toString()), format));
			return;
		}
		if (value instanceof Double) {
			sheet.addCell(new Number(row, line, Double.parseDouble(value.toString()), format));
			return;
		}
		if (value instanceof BigDecimal) {
			sheet.addCell(new Number(row, line, Double.parseDouble(value.toString()), format));
			return;
		}
		if (value instanceof Integer) {
			sheet.addCell(new Number(row, line, Double.parseDouble(value.toString()), format));
			return;
		}
		
		if (value instanceof Date){
			sheet.addCell(new Label(row, line, sf.format(value), format));
			return;
		}
		
		if(isNumeric(value.toString())){
			sheet.addCell(new Number(row, line, Double.parseDouble(value.toString()), format));
			return;
		}
		
		if(isDate(value.toString())){
			String[] split = value.toString().trim().split(" ");
			sheet.addCell(new Label(row, line, split[0], format));
			return;
		}
		
		sheet.addCell(new Label(row, line, value.toString(), format));
	}
	
	
	public static boolean isNumeric(String str){ 
		
		//判断是不是数字0到9
	    Pattern pattern1 = Pattern.compile("[0-9]*"); 
	    if(pattern1.matcher(str).matches()){
	    	return true;
	    }
	    
	    //判断是不是小数
	    Pattern pattern3 = Pattern.compile("[0-9]*[.][0-9]*"); 
	    if(pattern3.matcher(str).matches()){
	    	return true;
	    }

	    //判断是不是负数
	    Pattern pattern2 = Pattern.compile("^-[0-9]*[.][0-9]*"); 
	    if(pattern2.matcher(str).matches()){
	    	return true;
	    }
	    
	    
	    return false;    
	 } 
	
	public static boolean isDate(String str){ 
		
		if(str==null){
			return false;
		}
		
		if(str.equals("")){
			return false;
		}
		
		str=str.trim().split(" ")[0];
		
		Pattern pattern = Pattern.compile("^(?:[0-9]{1,4}(?<!^0?0?0?0))-(?:0?[1-9]|1[0-2])-(?:0?[1-9]|1[0-9]|2[0-8]).*"); 
		if(pattern.matcher(str).matches()){
			return true;
		}
		
	    return false;    
	 } 
	
	
	public static Double objToDouble(Object str){
		if(str==null){
			return new Double(0);
		}
		return Double.parseDouble(str.toString());
	}
	
	public static BigDecimal doubleToDecimal(Object str){
		return BigDecimal.valueOf(objToDouble(str.toString()));
	}
	
	public static Date stringToDate(String str,String pattern){
		if(pattern==null||pattern.equals("")){
			pattern="yyyy-MM-dd";
		}
		
		DateLocaleConverter dc=new DateLocaleConverter(Locale.CHINA, pattern);
		return (Date)dc.convert(str);
	}
	
	public static String dateToString(Date str,String pattern){
		if(pattern==null||pattern.equals("")){
			pattern="yyyy-MM-dd";
		}
		
		StringLocaleConverter dc=new StringLocaleConverter(Locale.CHINA, pattern);
		return (String)dc.convert(str);
	}
	
	public static WritableCellFormat getColunmFormat() throws WriteException{
		WritableCellFormat format = new WritableCellFormat(NumberFormats.TEXT);
		//format.setBorder(Border.ALL, BorderLineStyle.THIN);
		format.setAlignment(Alignment.CENTRE);
		return format;
	}

}
